{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type='text/css'>\n",
       ".datatable table.frame { margin-bottom: 0; }\n",
       ".datatable table.frame thead { border-bottom: none; }\n",
       ".datatable table.frame tr.coltypes td {  color: #FFFFFF;  line-height: 6px;  padding: 0 0.5em;}\n",
       ".datatable .bool    { background: #DDDD99; }\n",
       ".datatable .object  { background: #565656; }\n",
       ".datatable .int     { background: #5D9E5D; }\n",
       ".datatable .float   { background: #4040CC; }\n",
       ".datatable .str     { background: #CC4040; }\n",
       ".datatable .row_index {  background: var(--jp-border-color3);  border-right: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  font-size: 9px;}\n",
       ".datatable .frame tr.coltypes .row_index {  background: var(--jp-border-color0);}\n",
       ".datatable th:nth-child(2) { padding-left: 12px; }\n",
       ".datatable .hellipsis {  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .vellipsis {  background: var(--jp-layout-color0);  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .na {  color: var(--jp-cell-editor-border-color);  font-size: 80%;}\n",
       ".datatable .footer { font-size: 9px; }\n",
       ".datatable .frame_dimensions {  background: var(--jp-border-color3);  border-top: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  display: inline-block;  opacity: 0.6;  padding: 1px 10px 1px 5px;}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import gc\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('max_columns', 100)\n",
    "pd.set_option('max_rows', 100)\n",
    "from tqdm.notebook import tqdm\n",
    "\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import GroupKFold, KFold\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "import lightgbm as lgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_csv('raw_data/train.csv')\n",
    "train = train.sort_values(by=['QUEUE_ID', 'DOTTING_TIME']).reset_index(drop=True)\n",
    "\n",
    "test = pd.read_csv('raw_data/evaluation_public.csv')\n",
    "test = test.sort_values(by=['ID', 'DOTTING_TIME']).reset_index(drop=True)\n",
    "\n",
    "sub_sample = pd.read_csv('raw_data/submit_example.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QUEUE_ID</th>\n",
       "      <th>CU</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>QUEUE_TYPE</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>CPU_USAGE</th>\n",
       "      <th>MEM_USAGE</th>\n",
       "      <th>LAUNCHING_JOB_NUMS</th>\n",
       "      <th>RUNNING_JOB_NUMS</th>\n",
       "      <th>SUCCEED_JOB_NUMS</th>\n",
       "      <th>CANCELLED_JOB_NUMS</th>\n",
       "      <th>FAILED_JOB_NUMS</th>\n",
       "      <th>DOTTING_TIME</th>\n",
       "      <th>RESOURCE_TYPE</th>\n",
       "      <th>DISK_USAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>3</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590683100000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590683400000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>7</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590683700000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590684000000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590684120000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590684420000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590684720000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590685020000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590685320000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>6</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1590685620000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   QUEUE_ID  CU     STATUS QUEUE_TYPE PLATFORM  CPU_USAGE  MEM_USAGE  \\\n",
       "0         2  16  available        sql   x86_64          3         54   \n",
       "1         2  16  available        sql   x86_64          2         54   \n",
       "2         2  16  available        sql   x86_64          7         54   \n",
       "3         2  16  available        sql   x86_64          4         54   \n",
       "4         2  16  available        sql   x86_64          5         54   \n",
       "5         2  16  available        sql   x86_64          3         55   \n",
       "6         2  16  available        sql   x86_64          2         54   \n",
       "7         2  16  available        sql   x86_64          2         54   \n",
       "8         2  16  available        sql   x86_64          5         54   \n",
       "9         2  16  available        sql   x86_64          6         54   \n",
       "\n",
       "   LAUNCHING_JOB_NUMS  RUNNING_JOB_NUMS  SUCCEED_JOB_NUMS  CANCELLED_JOB_NUMS  \\\n",
       "0                   0                 0                 0                   0   \n",
       "1                   0                 0                 0                   0   \n",
       "2                   0                 0                 0                   0   \n",
       "3                   0                 0                 0                   0   \n",
       "4                   0                 0                 0                   0   \n",
       "5                   0                 0                 0                   0   \n",
       "6                   0                 0                 0                   0   \n",
       "7                   0                 0                 0                   0   \n",
       "8                   0                 0                 0                   0   \n",
       "9                   0                 0                 0                   0   \n",
       "\n",
       "   FAILED_JOB_NUMS   DOTTING_TIME RESOURCE_TYPE  DISK_USAGE  \n",
       "0                0  1590683100000            vm        20.0  \n",
       "1                0  1590683400000            vm        20.0  \n",
       "2                0  1590683700000            vm        20.0  \n",
       "3                0  1590684000000            vm        20.0  \n",
       "4                0  1590684120000            vm        20.0  \n",
       "5                0  1590684420000            vm        20.0  \n",
       "6                0  1590684720000            vm        20.0  \n",
       "7                0  1590685020000            vm        20.0  \n",
       "8                0  1590685320000            vm        20.0  \n",
       "9                0  1590685620000            vm        20.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>QUEUE_ID</th>\n",
       "      <th>CU</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>QUEUE_TYPE</th>\n",
       "      <th>PLATFORM</th>\n",
       "      <th>CPU_USAGE</th>\n",
       "      <th>MEM_USAGE</th>\n",
       "      <th>LAUNCHING_JOB_NUMS</th>\n",
       "      <th>RUNNING_JOB_NUMS</th>\n",
       "      <th>SUCCEED_JOB_NUMS</th>\n",
       "      <th>CANCELLED_JOB_NUMS</th>\n",
       "      <th>FAILED_JOB_NUMS</th>\n",
       "      <th>DOTTING_TIME</th>\n",
       "      <th>RESOURCE_TYPE</th>\n",
       "      <th>DISK_USAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>60</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1662213420000</td>\n",
       "      <td>vm</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>58</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1662213720000</td>\n",
       "      <td>vm</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>80</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1662214020000</td>\n",
       "      <td>vm</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1662214320000</td>\n",
       "      <td>vm</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>16</td>\n",
       "      <td>available</td>\n",
       "      <td>sql</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>98</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1662214620000</td>\n",
       "      <td>vm</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>85153</td>\n",
       "      <td>64</td>\n",
       "      <td>available</td>\n",
       "      <td>general</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>56</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1613655960000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>85153</td>\n",
       "      <td>64</td>\n",
       "      <td>available</td>\n",
       "      <td>general</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>48</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1613656260000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>85153</td>\n",
       "      <td>64</td>\n",
       "      <td>available</td>\n",
       "      <td>general</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>23</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1613656560000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>85153</td>\n",
       "      <td>64</td>\n",
       "      <td>available</td>\n",
       "      <td>general</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>68</td>\n",
       "      <td>61</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1613656860000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>85153</td>\n",
       "      <td>64</td>\n",
       "      <td>available</td>\n",
       "      <td>general</td>\n",
       "      <td>x86_64</td>\n",
       "      <td>38</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1613657160000</td>\n",
       "      <td>vm</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  QUEUE_ID  CU     STATUS QUEUE_TYPE PLATFORM  CPU_USAGE  MEM_USAGE  \\\n",
       "0   1       297  16  available        sql   x86_64         60         69   \n",
       "1   1       297  16  available        sql   x86_64         58         69   \n",
       "2   1       297  16  available        sql   x86_64         80         67   \n",
       "3   1       297  16  available        sql   x86_64        100         65   \n",
       "4   1       297  16  available        sql   x86_64         98         67   \n",
       "5   2     85153  64  available    general   x86_64         56         91   \n",
       "6   2     85153  64  available    general   x86_64         48         78   \n",
       "7   2     85153  64  available    general   x86_64         23         35   \n",
       "8   2     85153  64  available    general   x86_64         68         61   \n",
       "9   2     85153  64  available    general   x86_64         38         74   \n",
       "\n",
       "   LAUNCHING_JOB_NUMS  RUNNING_JOB_NUMS  SUCCEED_JOB_NUMS  CANCELLED_JOB_NUMS  \\\n",
       "0                   0                 5                 5                   0   \n",
       "1                   0                 9                 4                   0   \n",
       "2                   0                 9                 1                   0   \n",
       "3                   0                 7                 2                   0   \n",
       "4                   0                10                 3                   0   \n",
       "5                   0                 0                 0                   0   \n",
       "6                   0                 1                 1                   0   \n",
       "7                   0                 0                 0                   0   \n",
       "8                   0                 0                 0                   0   \n",
       "9                   0                 0                 0                   0   \n",
       "\n",
       "   FAILED_JOB_NUMS   DOTTING_TIME RESOURCE_TYPE  DISK_USAGE  \n",
       "0                0  1662213420000            vm           9  \n",
       "1                0  1662213720000            vm           9  \n",
       "2                0  1662214020000            vm           9  \n",
       "3                1  1662214320000            vm           9  \n",
       "4                1  1662214620000            vm           9  \n",
       "5                0  1613655960000            vm          20  \n",
       "6                0  1613656260000            vm          20  \n",
       "7                0  1613656560000            vm          20  \n",
       "8                0  1613656860000            vm          20  \n",
       "9                0  1613657160000            vm          20  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>CPU_USAGE_1</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_1</th>\n",
       "      <th>CPU_USAGE_2</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_2</th>\n",
       "      <th>CPU_USAGE_3</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_3</th>\n",
       "      <th>CPU_USAGE_4</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_4</th>\n",
       "      <th>CPU_USAGE_5</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  CPU_USAGE_1  LAUNCHING_JOB_NUMS_1  CPU_USAGE_2  LAUNCHING_JOB_NUMS_2  \\\n",
       "0   1            0                     0            0                     0   \n",
       "1   2            0                     0            0                     0   \n",
       "2   3            0                     0            0                     0   \n",
       "3   4            0                     0            0                     0   \n",
       "4   5            0                     0            0                     0   \n",
       "\n",
       "   CPU_USAGE_3  LAUNCHING_JOB_NUMS_3  CPU_USAGE_4  LAUNCHING_JOB_NUMS_4  \\\n",
       "0            0                     0            0                     0   \n",
       "1            0                     0            0                     0   \n",
       "2            0                     0            0                     0   \n",
       "3            0                     0            0                     0   \n",
       "4            0                     0            0                     0   \n",
       "\n",
       "   CPU_USAGE_5  LAUNCHING_JOB_NUMS_5  \n",
       "0            0                     0  \n",
       "1            0                     0  \n",
       "2            0                     0  \n",
       "3            0                     0  \n",
       "4            0                     0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub_sample.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((501730, 15), (14980, 16), (2996, 11))"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, test.shape, sub_sample.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这些 columns 在 test 只有单一值, 所以直接去掉\n",
    "\n",
    "del train['STATUS']\n",
    "del train['PLATFORM']\n",
    "del train['RESOURCE_TYPE']\n",
    "\n",
    "del test['STATUS']\n",
    "del test['PLATFORM']\n",
    "del test['RESOURCE_TYPE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间排序好后也没什么用了\n",
    "\n",
    "del train['DOTTING_TIME']\n",
    "del test['DOTTING_TIME']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Label Encoding\n",
    "\n",
    "le = LabelEncoder()\n",
    "train['QUEUE_TYPE'] = le.fit_transform(train['QUEUE_TYPE'].astype(str))\n",
    "test['QUEUE_TYPE'] = le.transform(test['QUEUE_TYPE'].astype(str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 只用 CPU_USAGE 和 MEM_USAGE\n",
    "to_drop_cols = [col for col in train.columns if col.endswith('_JOB_NUMS')] + ['DISK_USAGE']\n",
    "\n",
    "train.drop(to_drop_cols, axis=1, inplace=True)\n",
    "test.drop(to_drop_cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QUEUE_ID</th>\n",
       "      <th>CU</th>\n",
       "      <th>QUEUE_TYPE</th>\n",
       "      <th>CPU_USAGE</th>\n",
       "      <th>MEM_USAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   QUEUE_ID  CU  QUEUE_TYPE  CPU_USAGE  MEM_USAGE\n",
       "0         2  16           2          3         54\n",
       "1         2  16           2          2         54\n",
       "2         2  16           2          7         54\n",
       "3         2  16           2          4         54\n",
       "4         2  16           2          5         54\n",
       "5         2  16           2          3         55\n",
       "6         2  16           2          2         54\n",
       "7         2  16           2          2         54\n",
       "8         2  16           2          5         54\n",
       "9         2  16           2          6         54"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "29bec9b42237491b8e72b08328b709d0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=43.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QUEUE_ID: 2, lines: 19245\n",
      "QUEUE_ID: 3, lines: 19247\n",
      "QUEUE_ID: 4, lines: 19247\n",
      "QUEUE_ID: 26, lines: 10397\n",
      "QUEUE_ID: 27, lines: 10516\n",
      "QUEUE_ID: 36, lines: 3232\n",
      "QUEUE_ID: 233, lines: 2089\n",
      "QUEUE_ID: 281, lines: 10355\n",
      "QUEUE_ID: 287, lines: 6866\n",
      "QUEUE_ID: 291, lines: 8874\n",
      "QUEUE_ID: 293, lines: 8845\n",
      "QUEUE_ID: 297, lines: 21174\n",
      "QUEUE_ID: 298, lines: 20371\n",
      "QUEUE_ID: 20889, lines: 13995\n",
      "QUEUE_ID: 21487, lines: 28921\n",
      "QUEUE_ID: 21671, lines: 28085\n",
      "QUEUE_ID: 21673, lines: 19716\n",
      "QUEUE_ID: 21825, lines: 19713\n",
      "QUEUE_ID: 81221, lines: 19771\n",
      "QUEUE_ID: 82695, lines: 19716\n",
      "QUEUE_ID: 82697, lines: 10632\n",
      "QUEUE_ID: 82929, lines: 10189\n",
      "QUEUE_ID: 83109, lines: 8948\n",
      "QUEUE_ID: 83609, lines: 2110\n",
      "QUEUE_ID: 84151, lines: 11847\n",
      "QUEUE_ID: 84387, lines: 17510\n",
      "QUEUE_ID: 84907, lines: 6485\n",
      "QUEUE_ID: 85101, lines: 6608\n",
      "QUEUE_ID: 85153, lines: 14343\n",
      "QUEUE_ID: 85265, lines: 13506\n",
      "QUEUE_ID: 85267, lines: 13072\n",
      "QUEUE_ID: 85617, lines: 2543\n",
      "QUEUE_ID: 85619, lines: 9987\n",
      "QUEUE_ID: 85693, lines: 10824\n",
      "QUEUE_ID: 85731, lines: 8558\n",
      "QUEUE_ID: 85781, lines: 1139\n",
      "QUEUE_ID: 85915, lines: 9210\n",
      "QUEUE_ID: 85933, lines: 8801\n",
      "QUEUE_ID: 85977, lines: 8783\n",
      "QUEUE_ID: 86085, lines: 6185\n",
      "QUEUE_ID: 86865, lines: 3534\n",
      "QUEUE_ID: 86867, lines: 3743\n",
      "QUEUE_ID: 87139, lines: 2368\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# t0 t1 t2 t3 t4  ->  t5 t6 t7 t8 t9 \n",
    "# t1 t2 t3 t4 t5  ->  t6 t7 t8 t9 t10\n",
    "\n",
    "df_train = pd.DataFrame()\n",
    "\n",
    "for id_ in tqdm(train.QUEUE_ID.unique()):\n",
    "    df_tmp = train[train.QUEUE_ID == id_]\n",
    "    features = list()\n",
    "    t_cpu = list()\n",
    "    values = df_tmp.values\n",
    "    for i, _ in enumerate(values):\n",
    "        if i + 10 < len(values):\n",
    "            li_v = list()\n",
    "            li_v.append(values[i][0])\n",
    "            li_cpu = list()\n",
    "            for j in range(5):\n",
    "                li_v.extend(values[i+j][3:].tolist())\n",
    "                li_cpu.append(values[i+j+5][3])\n",
    "            features.append(li_v)\n",
    "            t_cpu.append(li_cpu)\n",
    "    df_feat = pd.DataFrame(features)\n",
    "    df_feat.columns = ['QUEUE_ID', \n",
    "                       'CPU_USAGE_1', 'MEM_USAGE_1', \n",
    "                       'CPU_USAGE_2', 'MEM_USAGE_2', \n",
    "                       'CPU_USAGE_3', 'MEM_USAGE_3', \n",
    "                       'CPU_USAGE_4', 'MEM_USAGE_4', \n",
    "                       'CPU_USAGE_5', 'MEM_USAGE_5', \n",
    "                      ]\n",
    "    df_cpu = pd.DataFrame(t_cpu)\n",
    "    df_cpu.columns = ['cpu_1', 'cpu_2', 'cpu_3', 'cpu_4', 'cpu_5']\n",
    "    df = pd.concat([df_feat, df_cpu], axis=1)\n",
    "    print(f'QUEUE_ID: {id_}, lines: {df.shape[0]}')\n",
    "    df_train = df_train.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0caf009dd1ea4cbe97f7a767c0c10b00",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=23.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QUEUE_ID: 297, lines: 1142\n",
      "QUEUE_ID: 85153, lines: 390\n",
      "QUEUE_ID: 291, lines: 57\n",
      "QUEUE_ID: 21487, lines: 447\n",
      "QUEUE_ID: 85265, lines: 19\n",
      "QUEUE_ID: 4, lines: 151\n",
      "QUEUE_ID: 2, lines: 151\n",
      "QUEUE_ID: 81221, lines: 52\n",
      "QUEUE_ID: 287, lines: 53\n",
      "QUEUE_ID: 85693, lines: 24\n",
      "QUEUE_ID: 3, lines: 156\n",
      "QUEUE_ID: 293, lines: 51\n",
      "QUEUE_ID: 36, lines: 33\n",
      "QUEUE_ID: 26, lines: 58\n",
      "QUEUE_ID: 281, lines: 100\n",
      "QUEUE_ID: 83609, lines: 3\n",
      "QUEUE_ID: 21671, lines: 29\n",
      "QUEUE_ID: 27, lines: 57\n",
      "QUEUE_ID: 233, lines: 11\n",
      "QUEUE_ID: 85101, lines: 2\n",
      "QUEUE_ID: 85933, lines: 4\n",
      "QUEUE_ID: 21673, lines: 4\n",
      "QUEUE_ID: 298, lines: 2\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_test = pd.DataFrame()\n",
    "\n",
    "for id_ in tqdm(test.QUEUE_ID.unique()):\n",
    "    df_tmp = test[test.QUEUE_ID == id_]\n",
    "    features = list()\n",
    "    values = df_tmp.values\n",
    "    for i, _ in enumerate(values):\n",
    "        if i % 5 == 0:\n",
    "            li_v = list()\n",
    "            li_v.append(values[i][0])\n",
    "            li_v.append(values[i][1])\n",
    "            for j in range(5):\n",
    "                li_v.extend(values[i+j][4:].tolist())\n",
    "            features.append(li_v)\n",
    "    df_feat = pd.DataFrame(features)\n",
    "    df_feat.columns = ['ID', 'QUEUE_ID', \n",
    "                       'CPU_USAGE_1', 'MEM_USAGE_1', \n",
    "                       'CPU_USAGE_2', 'MEM_USAGE_2', \n",
    "                       'CPU_USAGE_3', 'MEM_USAGE_3', \n",
    "                       'CPU_USAGE_4', 'MEM_USAGE_4', \n",
    "                       'CPU_USAGE_5', 'MEM_USAGE_5', \n",
    "                      ]\n",
    "    df = df_feat.copy()\n",
    "    print(f'QUEUE_ID: {id_}, lines: {df.shape[0]}')\n",
    "    df_test = df_test.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 行内统计特征\n",
    "\n",
    "df_train['cpu_mean'] = df_train[[f'CPU_USAGE_{i}' for i in range(1,6)]].mean(axis=1)\n",
    "df_train['cpu_std'] = df_train[[f'CPU_USAGE_{i}' for i in range(1,6)]].std(axis=1)\n",
    "df_train['cpu_diff'] = df_train['CPU_USAGE_5'] - df_train['CPU_USAGE_1']\n",
    "df_train['cpu_max'] = df_train[[f'CPU_USAGE_{i}' for i in range(1,6)]].max(axis=1)\n",
    "df_train['mem_mean'] = df_train[[f'MEM_USAGE_{i}' for i in range(1,6)]].mean(axis=1)\n",
    "df_train['mem_std'] = df_train[[f'MEM_USAGE_{i}' for i in range(1,6)]].std(axis=1)\n",
    "df_train['mem_max'] = df_train[[f'MEM_USAGE_{i}' for i in range(1,6)]].max(axis=1)\n",
    "\n",
    "df_test['cpu_mean'] = df_test[[f'CPU_USAGE_{i}' for i in range(1,6)]].mean(axis=1)\n",
    "df_test['cpu_std'] = df_test[[f'CPU_USAGE_{i}' for i in range(1,6)]].std(axis=1)\n",
    "df_test['cpu_diff'] = df_test['CPU_USAGE_5'] - df_test['CPU_USAGE_1']\n",
    "df_test['cpu_max'] = df_test[[f'CPU_USAGE_{i}' for i in range(1,6)]].max(axis=1)\n",
    "df_test['mem_mean'] = df_test[[f'MEM_USAGE_{i}' for i in range(1,6)]].mean(axis=1)\n",
    "df_test['mem_std'] = df_test[[f'MEM_USAGE_{i}' for i in range(1,6)]].std(axis=1)\n",
    "df_test['mem_max'] = df_test[[f'MEM_USAGE_{i}' for i in range(1,6)]].max(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(501300, 23)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QUEUE_ID</th>\n",
       "      <th>CPU_USAGE_1</th>\n",
       "      <th>MEM_USAGE_1</th>\n",
       "      <th>CPU_USAGE_2</th>\n",
       "      <th>MEM_USAGE_2</th>\n",
       "      <th>CPU_USAGE_3</th>\n",
       "      <th>MEM_USAGE_3</th>\n",
       "      <th>CPU_USAGE_4</th>\n",
       "      <th>MEM_USAGE_4</th>\n",
       "      <th>CPU_USAGE_5</th>\n",
       "      <th>MEM_USAGE_5</th>\n",
       "      <th>cpu_1</th>\n",
       "      <th>cpu_2</th>\n",
       "      <th>cpu_3</th>\n",
       "      <th>cpu_4</th>\n",
       "      <th>cpu_5</th>\n",
       "      <th>cpu_mean</th>\n",
       "      <th>cpu_std</th>\n",
       "      <th>cpu_diff</th>\n",
       "      <th>cpu_max</th>\n",
       "      <th>mem_mean</th>\n",
       "      <th>mem_std</th>\n",
       "      <th>mem_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>54</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>7</td>\n",
       "      <td>54</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1.923538</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>7</td>\n",
       "      <td>54</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1.923538</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>54.2</td>\n",
       "      <td>0.447214</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>54</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1.923538</td>\n",
       "      <td>-5</td>\n",
       "      <td>7</td>\n",
       "      <td>54.2</td>\n",
       "      <td>0.447214</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.303840</td>\n",
       "      <td>-2</td>\n",
       "      <td>5</td>\n",
       "      <td>54.2</td>\n",
       "      <td>0.447214</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>2</td>\n",
       "      <td>54</td>\n",
       "      <td>5</td>\n",
       "      <td>54</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.516575</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>54.2</td>\n",
       "      <td>0.447214</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   QUEUE_ID  CPU_USAGE_1  MEM_USAGE_1  CPU_USAGE_2  MEM_USAGE_2  CPU_USAGE_3  \\\n",
       "0         2            3           54            2           54            7   \n",
       "1         2            2           54            7           54            4   \n",
       "2         2            7           54            4           54            5   \n",
       "3         2            4           54            5           54            3   \n",
       "4         2            5           54            3           55            2   \n",
       "\n",
       "   MEM_USAGE_3  CPU_USAGE_4  MEM_USAGE_4  CPU_USAGE_5  MEM_USAGE_5  cpu_1  \\\n",
       "0           54            4           54            5           54      3   \n",
       "1           54            5           54            3           55      2   \n",
       "2           54            3           55            2           54      2   \n",
       "3           55            2           54            2           54      5   \n",
       "4           54            2           54            5           54      6   \n",
       "\n",
       "   cpu_2  cpu_3  cpu_4  cpu_5  cpu_mean   cpu_std  cpu_diff  cpu_max  \\\n",
       "0      2      2      5      6       4.2  1.923538         2        7   \n",
       "1      2      5      6      2       4.2  1.923538         1        7   \n",
       "2      5      6      2      3       4.2  1.923538        -5        7   \n",
       "3      6      2      3     10       3.2  1.303840        -2        5   \n",
       "4      2      3     10      6       3.4  1.516575         0        5   \n",
       "\n",
       "   mem_mean   mem_std  mem_max  \n",
       "0      54.0  0.000000       54  \n",
       "1      54.2  0.447214       55  \n",
       "2      54.2  0.447214       55  \n",
       "3      54.2  0.447214       55  \n",
       "4      54.2  0.447214       55  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_train.shape)\n",
    "df_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2996, 19)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>QUEUE_ID</th>\n",
       "      <th>CPU_USAGE_1</th>\n",
       "      <th>MEM_USAGE_1</th>\n",
       "      <th>CPU_USAGE_2</th>\n",
       "      <th>MEM_USAGE_2</th>\n",
       "      <th>CPU_USAGE_3</th>\n",
       "      <th>MEM_USAGE_3</th>\n",
       "      <th>CPU_USAGE_4</th>\n",
       "      <th>MEM_USAGE_4</th>\n",
       "      <th>CPU_USAGE_5</th>\n",
       "      <th>MEM_USAGE_5</th>\n",
       "      <th>cpu_mean</th>\n",
       "      <th>cpu_std</th>\n",
       "      <th>cpu_diff</th>\n",
       "      <th>cpu_max</th>\n",
       "      <th>mem_mean</th>\n",
       "      <th>mem_std</th>\n",
       "      <th>mem_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>297</td>\n",
       "      <td>60</td>\n",
       "      <td>69</td>\n",
       "      <td>58</td>\n",
       "      <td>69</td>\n",
       "      <td>80</td>\n",
       "      <td>67</td>\n",
       "      <td>100</td>\n",
       "      <td>65</td>\n",
       "      <td>98</td>\n",
       "      <td>67</td>\n",
       "      <td>79.2</td>\n",
       "      <td>20.029978</td>\n",
       "      <td>38</td>\n",
       "      <td>100</td>\n",
       "      <td>67.4</td>\n",
       "      <td>1.673320</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>297</td>\n",
       "      <td>2</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "      <td>42</td>\n",
       "      <td>3</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>90</td>\n",
       "      <td>82</td>\n",
       "      <td>36.0</td>\n",
       "      <td>44.883182</td>\n",
       "      <td>88</td>\n",
       "      <td>90</td>\n",
       "      <td>55.2</td>\n",
       "      <td>18.753666</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>297</td>\n",
       "      <td>3</td>\n",
       "      <td>34</td>\n",
       "      <td>37</td>\n",
       "      <td>46</td>\n",
       "      <td>90</td>\n",
       "      <td>71</td>\n",
       "      <td>64</td>\n",
       "      <td>72</td>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "      <td>52.4</td>\n",
       "      <td>33.426038</td>\n",
       "      <td>65</td>\n",
       "      <td>90</td>\n",
       "      <td>58.2</td>\n",
       "      <td>17.210462</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>297</td>\n",
       "      <td>32</td>\n",
       "      <td>51</td>\n",
       "      <td>84</td>\n",
       "      <td>78</td>\n",
       "      <td>52</td>\n",
       "      <td>80</td>\n",
       "      <td>31</td>\n",
       "      <td>80</td>\n",
       "      <td>32</td>\n",
       "      <td>77</td>\n",
       "      <td>46.2</td>\n",
       "      <td>22.895414</td>\n",
       "      <td>0</td>\n",
       "      <td>84</td>\n",
       "      <td>73.2</td>\n",
       "      <td>12.477981</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>297</td>\n",
       "      <td>50</td>\n",
       "      <td>67</td>\n",
       "      <td>69</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>47</td>\n",
       "      <td>25.0</td>\n",
       "      <td>32.202484</td>\n",
       "      <td>-48</td>\n",
       "      <td>69</td>\n",
       "      <td>61.2</td>\n",
       "      <td>8.043631</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  QUEUE_ID  CPU_USAGE_1  MEM_USAGE_1  CPU_USAGE_2  MEM_USAGE_2  \\\n",
       "0   1       297           60           69           58           69   \n",
       "1   3       297            2           41            5           42   \n",
       "2   4       297            3           34           37           46   \n",
       "3   7       297           32           51           84           78   \n",
       "4  11       297           50           67           69           64   \n",
       "\n",
       "   CPU_USAGE_3  MEM_USAGE_3  CPU_USAGE_4  MEM_USAGE_4  CPU_USAGE_5  \\\n",
       "0           80           67          100           65           98   \n",
       "1            3           43           80           68           90   \n",
       "2           90           71           64           72           68   \n",
       "3           52           80           31           80           32   \n",
       "4            2           64            2           64            2   \n",
       "\n",
       "   MEM_USAGE_5  cpu_mean    cpu_std  cpu_diff  cpu_max  mem_mean    mem_std  \\\n",
       "0           67      79.2  20.029978        38      100      67.4   1.673320   \n",
       "1           82      36.0  44.883182        88       90      55.2  18.753666   \n",
       "2           68      52.4  33.426038        65       90      58.2  17.210462   \n",
       "3           77      46.2  22.895414         0       84      73.2  12.477981   \n",
       "4           47      25.0  32.202484       -48       69      61.2   8.043631   \n",
       "\n",
       "   mem_max  \n",
       "0       69  \n",
       "1       82  \n",
       "2       72  \n",
       "3       80  \n",
       "4       67  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df_test.shape)\n",
    "df_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def run_lgb_qid(df_train, df_test, target, qid):\n",
    "    \n",
    "    feature_names = list(\n",
    "        filter(lambda x: x not in ['QUEUE_ID', 'CU', 'QUEUE_TYPE'] + [f'cpu_{i}' for i in range(1,6)], \n",
    "               df_train.columns))\n",
    "    \n",
    "    # 提取 QUEUE_ID 对应的数据集\n",
    "    df_train = df_train[df_train.QUEUE_ID == qid]\n",
    "    df_test = df_test[df_test.QUEUE_ID == qid]\n",
    "    \n",
    "    print(f\"QUEUE_ID:{qid}, target:{target}, train:{len(df_train)}, test:{len(df_test)}\")\n",
    "    \n",
    "    model = lgb.LGBMRegressor(num_leaves=20,\n",
    "                              max_depth=4,\n",
    "                              learning_rate=0.08,\n",
    "                              n_estimators=10000,\n",
    "                              subsample=0.9,\n",
    "                              feature_fraction=0.8,\n",
    "                              reg_alpha=0.6,\n",
    "                              reg_lambda=1.2,\n",
    "                              random_state=42)\n",
    "    oof = []\n",
    "    prediction = df_test[['ID', 'QUEUE_ID']]\n",
    "    prediction[target] = 0\n",
    "    \n",
    "    kfold = KFold(n_splits=5, random_state=42)\n",
    "    for fold_id, (trn_idx, val_idx) in enumerate(kfold.split(df_train, df_train[target])):\n",
    "        \n",
    "        X_train = df_train.iloc[trn_idx][feature_names]\n",
    "        Y_train = df_train.iloc[trn_idx][target]\n",
    "        X_val = df_train.iloc[val_idx][feature_names]\n",
    "        Y_val = df_train.iloc[val_idx][target]\n",
    "        \n",
    "        lgb_model = model.fit(X_train, \n",
    "                              Y_train,\n",
    "                              eval_names=['train', 'valid'],\n",
    "                              eval_set=[(X_train, Y_train), (X_val, Y_val)],\n",
    "                              verbose=0,\n",
    "                              eval_metric='mse',\n",
    "                              early_stopping_rounds=20)\n",
    "        \n",
    "        pred_val = lgb_model.predict(X_val, num_iteration=lgb_model.best_iteration_)\n",
    "        df_oof = df_train.iloc[val_idx][[target, 'QUEUE_ID']].copy()\n",
    "        df_oof['pred'] = pred_val\n",
    "        oof.append(df_oof)\n",
    "        \n",
    "        pred_test = lgb_model.predict(df_test[feature_names], num_iteration=lgb_model.best_iteration_)\n",
    "        prediction[target] += pred_test / kfold.n_splits\n",
    "        \n",
    "        del lgb_model, pred_val, pred_test, X_train, Y_train, X_val, Y_val\n",
    "        gc.collect()\n",
    "        \n",
    "    df_oof = pd.concat(oof)\n",
    "    score = mean_squared_error(df_oof[target], df_oof['pred'])\n",
    "    print('MSE:', score)\n",
    "\n",
    "    return prediction, score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "394b1bf87cdd429886078d0cc45ecb66",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=23.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QUEUE_ID:297, target:cpu_1, train:21174, test:1142\n",
      "MSE: 149.32876070057813\n",
      "QUEUE_ID:297, target:cpu_2, train:21174, test:1142\n",
      "MSE: 230.2215851475953\n",
      "QUEUE_ID:297, target:cpu_3, train:21174, test:1142\n",
      "MSE: 234.30335117861307\n",
      "QUEUE_ID:297, target:cpu_4, train:21174, test:1142\n",
      "MSE: 195.3317566185578\n",
      "QUEUE_ID:297, target:cpu_5, train:21174, test:1142\n",
      "MSE: 144.0756453053243\n",
      "QUEUE_ID:85153, target:cpu_1, train:14343, test:390\n",
      "MSE: 166.59306316840988\n",
      "QUEUE_ID:85153, target:cpu_2, train:14343, test:390\n",
      "MSE: 190.96021024410138\n",
      "QUEUE_ID:85153, target:cpu_3, train:14343, test:390\n",
      "MSE: 196.66236632719\n",
      "QUEUE_ID:85153, target:cpu_4, train:14343, test:390\n",
      "MSE: 200.01051626902412\n",
      "QUEUE_ID:85153, target:cpu_5, train:14343, test:390\n",
      "MSE: 202.92113111605087\n",
      "QUEUE_ID:291, target:cpu_1, train:8874, test:57\n",
      "MSE: 82.34531737776904\n",
      "QUEUE_ID:291, target:cpu_2, train:8874, test:57\n",
      "MSE: 178.4386014114649\n",
      "QUEUE_ID:291, target:cpu_3, train:8874, test:57\n",
      "MSE: 238.13296273801464\n",
      "QUEUE_ID:291, target:cpu_4, train:8874, test:57\n",
      "MSE: 272.71337941088996\n",
      "QUEUE_ID:291, target:cpu_5, train:8874, test:57\n",
      "MSE: 295.3194313383182\n",
      "QUEUE_ID:21487, target:cpu_1, train:28921, test:447\n",
      "MSE: 59.12967672810643\n",
      "QUEUE_ID:21487, target:cpu_2, train:28921, test:447\n",
      "MSE: 80.67140888835753\n",
      "QUEUE_ID:21487, target:cpu_3, train:28921, test:447\n",
      "MSE: 82.26301149966294\n",
      "QUEUE_ID:21487, target:cpu_4, train:28921, test:447\n",
      "MSE: 82.37924753039242\n",
      "QUEUE_ID:21487, target:cpu_5, train:28921, test:447\n",
      "MSE: 82.94665965555933\n",
      "QUEUE_ID:85265, target:cpu_1, train:13506, test:19\n",
      "MSE: 0.9611760685083046\n",
      "QUEUE_ID:85265, target:cpu_2, train:13506, test:19\n",
      "MSE: 1.0091166036236083\n",
      "QUEUE_ID:85265, target:cpu_3, train:13506, test:19\n",
      "MSE: 1.0558430352330685\n",
      "QUEUE_ID:85265, target:cpu_4, train:13506, test:19\n",
      "MSE: 1.0850269640018664\n",
      "QUEUE_ID:85265, target:cpu_5, train:13506, test:19\n",
      "MSE: 1.0963465776269583\n",
      "QUEUE_ID:4, target:cpu_1, train:19247, test:151\n",
      "MSE: 4.189511780016521\n",
      "QUEUE_ID:4, target:cpu_2, train:19247, test:151\n",
      "MSE: 4.202148975039276\n",
      "QUEUE_ID:4, target:cpu_3, train:19247, test:151\n",
      "MSE: 4.208796361089853\n",
      "QUEUE_ID:4, target:cpu_4, train:19247, test:151\n",
      "MSE: 4.220428776128925\n",
      "QUEUE_ID:4, target:cpu_5, train:19247, test:151\n",
      "MSE: 4.221528183492132\n",
      "QUEUE_ID:2, target:cpu_1, train:19245, test:151\n",
      "MSE: 5.8479908368586235\n",
      "QUEUE_ID:2, target:cpu_2, train:19245, test:151\n",
      "MSE: 5.796286537494198\n",
      "QUEUE_ID:2, target:cpu_3, train:19245, test:151\n",
      "MSE: 6.295764611140981\n",
      "QUEUE_ID:2, target:cpu_4, train:19245, test:151\n",
      "MSE: 6.7631658746897605\n",
      "QUEUE_ID:2, target:cpu_5, train:19245, test:151\n",
      "MSE: 6.9591492130570005\n",
      "QUEUE_ID:81221, target:cpu_1, train:19771, test:52\n",
      "MSE: 9.257975063920581\n",
      "QUEUE_ID:81221, target:cpu_2, train:19771, test:52\n",
      "MSE: 9.317970257823493\n",
      "QUEUE_ID:81221, target:cpu_3, train:19771, test:52\n",
      "MSE: 9.312413919442962\n",
      "QUEUE_ID:81221, target:cpu_4, train:19771, test:52\n",
      "MSE: 9.34464334745965\n",
      "QUEUE_ID:81221, target:cpu_5, train:19771, test:52\n",
      "MSE: 9.34197399980346\n",
      "QUEUE_ID:287, target:cpu_1, train:6866, test:53\n",
      "MSE: 64.44448274295364\n",
      "QUEUE_ID:287, target:cpu_2, train:6866, test:53\n",
      "MSE: 87.08709022887476\n",
      "QUEUE_ID:287, target:cpu_3, train:6866, test:53\n",
      "MSE: 114.44219634055553\n",
      "QUEUE_ID:287, target:cpu_4, train:6866, test:53\n",
      "MSE: 136.29454469556836\n",
      "QUEUE_ID:287, target:cpu_5, train:6866, test:53\n",
      "MSE: 147.99332144047597\n",
      "QUEUE_ID:85693, target:cpu_1, train:10824, test:24\n",
      "MSE: 20.83633773507199\n",
      "QUEUE_ID:85693, target:cpu_2, train:10824, test:24\n",
      "MSE: 24.15535575820125\n",
      "QUEUE_ID:85693, target:cpu_3, train:10824, test:24\n",
      "MSE: 25.209772610608304\n",
      "QUEUE_ID:85693, target:cpu_4, train:10824, test:24\n",
      "MSE: 25.47899799741903\n",
      "QUEUE_ID:85693, target:cpu_5, train:10824, test:24\n",
      "MSE: 25.410953447788163\n",
      "QUEUE_ID:3, target:cpu_1, train:19247, test:156\n",
      "MSE: 3.952511133390944\n",
      "QUEUE_ID:3, target:cpu_2, train:19247, test:156\n",
      "MSE: 3.948230671422256\n",
      "QUEUE_ID:3, target:cpu_3, train:19247, test:156\n",
      "MSE: 3.959602515080951\n",
      "QUEUE_ID:3, target:cpu_4, train:19247, test:156\n",
      "MSE: 3.966590979945195\n",
      "QUEUE_ID:3, target:cpu_5, train:19247, test:156\n",
      "MSE: 3.979463336496675\n",
      "QUEUE_ID:293, target:cpu_1, train:8845, test:51\n",
      "MSE: 47.52154101173917\n",
      "QUEUE_ID:293, target:cpu_2, train:8845, test:51\n",
      "MSE: 94.14320222331074\n",
      "QUEUE_ID:293, target:cpu_3, train:8845, test:51\n",
      "MSE: 122.79490380215165\n",
      "QUEUE_ID:293, target:cpu_4, train:8845, test:51\n",
      "MSE: 147.02148227472256\n",
      "QUEUE_ID:293, target:cpu_5, train:8845, test:51\n",
      "MSE: 155.7215802903889\n",
      "QUEUE_ID:36, target:cpu_1, train:3232, test:33\n",
      "MSE: 69.62031269111665\n",
      "QUEUE_ID:36, target:cpu_2, train:3232, test:33\n",
      "MSE: 88.65878563614224\n",
      "QUEUE_ID:36, target:cpu_3, train:3232, test:33\n",
      "MSE: 108.62424813811487\n",
      "QUEUE_ID:36, target:cpu_4, train:3232, test:33\n",
      "MSE: 128.94943691275878\n",
      "QUEUE_ID:36, target:cpu_5, train:3232, test:33\n",
      "MSE: 147.5548453155189\n",
      "QUEUE_ID:26, target:cpu_1, train:10397, test:58\n",
      "MSE: 0.17989868786255292\n",
      "QUEUE_ID:26, target:cpu_2, train:10397, test:58\n",
      "MSE: 0.1933604266102429\n",
      "QUEUE_ID:26, target:cpu_3, train:10397, test:58\n",
      "MSE: 0.1983432255794857\n",
      "QUEUE_ID:26, target:cpu_4, train:10397, test:58\n",
      "MSE: 0.19940014476566115\n",
      "QUEUE_ID:26, target:cpu_5, train:10397, test:58\n",
      "MSE: 0.20020759890689505\n",
      "QUEUE_ID:281, target:cpu_1, train:10355, test:100\n",
      "MSE: 0.4409704824550641\n",
      "QUEUE_ID:281, target:cpu_2, train:10355, test:100\n",
      "MSE: 0.5004062681918022\n",
      "QUEUE_ID:281, target:cpu_3, train:10355, test:100\n",
      "MSE: 0.5914108074527699\n",
      "QUEUE_ID:281, target:cpu_4, train:10355, test:100\n",
      "MSE: 0.540110853569181\n",
      "QUEUE_ID:281, target:cpu_5, train:10355, test:100\n",
      "MSE: 0.5080058198908256\n",
      "QUEUE_ID:83609, target:cpu_1, train:2110, test:3\n",
      "MSE: 72.31004028783751\n",
      "QUEUE_ID:83609, target:cpu_2, train:2110, test:3\n",
      "MSE: 70.95970683939284\n",
      "QUEUE_ID:83609, target:cpu_3, train:2110, test:3\n",
      "MSE: 71.77159963795025\n",
      "QUEUE_ID:83609, target:cpu_4, train:2110, test:3\n",
      "MSE: 72.34048492597708\n",
      "QUEUE_ID:83609, target:cpu_5, train:2110, test:3\n",
      "MSE: 71.1628169954176\n",
      "QUEUE_ID:21671, target:cpu_1, train:28085, test:29\n",
      "MSE: 3.519728551990709\n",
      "QUEUE_ID:21671, target:cpu_2, train:28085, test:29\n",
      "MSE: 5.763314411038915\n",
      "QUEUE_ID:21671, target:cpu_3, train:28085, test:29\n",
      "MSE: 5.768129271609988\n",
      "QUEUE_ID:21671, target:cpu_4, train:28085, test:29\n",
      "MSE: 5.149438764793862\n",
      "QUEUE_ID:21671, target:cpu_5, train:28085, test:29\n",
      "MSE: 4.479078733565388\n",
      "QUEUE_ID:27, target:cpu_1, train:10516, test:57\n",
      "MSE: 0.6726482761046918\n",
      "QUEUE_ID:27, target:cpu_2, train:10516, test:57\n",
      "MSE: 0.6714607751988939\n",
      "QUEUE_ID:27, target:cpu_3, train:10516, test:57\n",
      "MSE: 0.8357010972563076\n",
      "QUEUE_ID:27, target:cpu_4, train:10516, test:57\n",
      "MSE: 0.8704744608967144\n",
      "QUEUE_ID:27, target:cpu_5, train:10516, test:57\n",
      "MSE: 0.8705376704129749\n",
      "QUEUE_ID:233, target:cpu_1, train:2089, test:11\n",
      "MSE: 67.03160239310183\n",
      "QUEUE_ID:233, target:cpu_2, train:2089, test:11\n",
      "MSE: 83.22240485094581\n",
      "QUEUE_ID:233, target:cpu_3, train:2089, test:11\n",
      "MSE: 105.2555032762481\n",
      "QUEUE_ID:233, target:cpu_4, train:2089, test:11\n",
      "MSE: 129.19704865303373\n",
      "QUEUE_ID:233, target:cpu_5, train:2089, test:11\n",
      "MSE: 152.71718470770435\n",
      "QUEUE_ID:85101, target:cpu_1, train:6608, test:2\n",
      "MSE: 0.8129907194763316\n",
      "QUEUE_ID:85101, target:cpu_2, train:6608, test:2\n",
      "MSE: 0.8504794822460245\n",
      "QUEUE_ID:85101, target:cpu_3, train:6608, test:2\n",
      "MSE: 0.8585736025839928\n",
      "QUEUE_ID:85101, target:cpu_4, train:6608, test:2\n",
      "MSE: 0.8651855685220201\n",
      "QUEUE_ID:85101, target:cpu_5, train:6608, test:2\n",
      "MSE: 0.8609944896521149\n",
      "QUEUE_ID:85933, target:cpu_1, train:8801, test:4\n",
      "MSE: 21.416561163544397\n",
      "QUEUE_ID:85933, target:cpu_2, train:8801, test:4\n",
      "MSE: 28.310772879348065\n",
      "QUEUE_ID:85933, target:cpu_3, train:8801, test:4\n",
      "MSE: 36.663349259547566\n",
      "QUEUE_ID:85933, target:cpu_4, train:8801, test:4\n",
      "MSE: 37.1885191637539\n",
      "QUEUE_ID:85933, target:cpu_5, train:8801, test:4\n",
      "MSE: 40.143051005105086\n",
      "QUEUE_ID:21673, target:cpu_1, train:19716, test:4\n",
      "MSE: 3.4577511754650403\n",
      "QUEUE_ID:21673, target:cpu_2, train:19716, test:4\n",
      "MSE: 3.5471024493840084\n",
      "QUEUE_ID:21673, target:cpu_3, train:19716, test:4\n",
      "MSE: 3.5496132077434126\n",
      "QUEUE_ID:21673, target:cpu_4, train:19716, test:4\n",
      "MSE: 3.5475792011059286\n",
      "QUEUE_ID:21673, target:cpu_5, train:19716, test:4\n",
      "MSE: 3.552479264197568\n",
      "QUEUE_ID:298, target:cpu_1, train:20371, test:2\n",
      "MSE: 7.9732083351917025\n",
      "QUEUE_ID:298, target:cpu_2, train:20371, test:2\n",
      "MSE: 8.095720224119521\n",
      "QUEUE_ID:298, target:cpu_3, train:20371, test:2\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 7.906314894555713\n",
      "QUEUE_ID:298, target:cpu_4, train:20371, test:2\n",
      "MSE: 8.096041288743692\n",
      "QUEUE_ID:298, target:cpu_5, train:20371, test:2\n",
      "MSE: 8.177420585373383\n",
      "\n"
     ]
    }
   ],
   "source": [
    "predictions = list()\n",
    "scores = list()\n",
    "\n",
    "for qid in tqdm(test.QUEUE_ID.unique()):    \n",
    "    df = pd.DataFrame()\n",
    "    for t in [f'cpu_{i}' for i in range(1,6)]:\n",
    "        prediction, score = run_lgb_qid(df_train, df_test, target=t, qid=qid)\n",
    "        if t == 'cpu_1':\n",
    "            df = prediction.copy()\n",
    "        else:\n",
    "            df = pd.merge(df, prediction, on=['ID', 'QUEUE_ID'], how='left')            \n",
    "        scores.append(score)\n",
    "\n",
    "    predictions.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean MSE score:  55.86956396891888\n"
     ]
    }
   ],
   "source": [
    "print('mean MSE score: ', np.mean(scores))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2996, 11)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>CPU_USAGE_1</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_1</th>\n",
       "      <th>CPU_USAGE_2</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_2</th>\n",
       "      <th>CPU_USAGE_3</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_3</th>\n",
       "      <th>CPU_USAGE_4</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_4</th>\n",
       "      <th>CPU_USAGE_5</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>90.795396</td>\n",
       "      <td>0</td>\n",
       "      <td>86.232249</td>\n",
       "      <td>0</td>\n",
       "      <td>85.889177</td>\n",
       "      <td>0</td>\n",
       "      <td>93.051595</td>\n",
       "      <td>0</td>\n",
       "      <td>92.070281</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>38.108620</td>\n",
       "      <td>0</td>\n",
       "      <td>32.646857</td>\n",
       "      <td>0</td>\n",
       "      <td>35.574923</td>\n",
       "      <td>0</td>\n",
       "      <td>42.289413</td>\n",
       "      <td>0</td>\n",
       "      <td>27.725214</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>38.876593</td>\n",
       "      <td>0</td>\n",
       "      <td>57.453038</td>\n",
       "      <td>0</td>\n",
       "      <td>13.975760</td>\n",
       "      <td>0</td>\n",
       "      <td>9.631157</td>\n",
       "      <td>0</td>\n",
       "      <td>1.637484</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>23.553786</td>\n",
       "      <td>0</td>\n",
       "      <td>16.824533</td>\n",
       "      <td>0</td>\n",
       "      <td>6.838082</td>\n",
       "      <td>0</td>\n",
       "      <td>4.016654</td>\n",
       "      <td>0</td>\n",
       "      <td>3.538453</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3.203495</td>\n",
       "      <td>0</td>\n",
       "      <td>6.925173</td>\n",
       "      <td>0</td>\n",
       "      <td>10.519199</td>\n",
       "      <td>0</td>\n",
       "      <td>10.891437</td>\n",
       "      <td>0</td>\n",
       "      <td>9.141628</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  CPU_USAGE_1  LAUNCHING_JOB_NUMS_1  CPU_USAGE_2  LAUNCHING_JOB_NUMS_2  \\\n",
       "0   1    90.795396                     0    86.232249                     0   \n",
       "1   2    38.108620                     0    32.646857                     0   \n",
       "2   3    38.876593                     0    57.453038                     0   \n",
       "3   4    23.553786                     0    16.824533                     0   \n",
       "4   5     3.203495                     0     6.925173                     0   \n",
       "\n",
       "   CPU_USAGE_3  LAUNCHING_JOB_NUMS_3  CPU_USAGE_4  LAUNCHING_JOB_NUMS_4  \\\n",
       "0    85.889177                     0    93.051595                     0   \n",
       "1    35.574923                     0    42.289413                     0   \n",
       "2    13.975760                     0     9.631157                     0   \n",
       "3     6.838082                     0     4.016654                     0   \n",
       "4    10.519199                     0    10.891437                     0   \n",
       "\n",
       "   CPU_USAGE_5  LAUNCHING_JOB_NUMS_5  \n",
       "0    92.070281                     0  \n",
       "1    27.725214                     0  \n",
       "2     1.637484                     0  \n",
       "3     3.538453                     0  \n",
       "4     9.141628                     0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub = pd.concat(predictions)\n",
    "\n",
    "sub = sub.sort_values(by='ID').reset_index(drop=True)\n",
    "sub.drop(['QUEUE_ID'], axis=1, inplace=True)\n",
    "sub.columns = ['ID'] + [f'CPU_USAGE_{i}' for i in range(1,6)]\n",
    "\n",
    "# 全置 0 都比训练出来的结果好\n",
    "for col in [f'LAUNCHING_JOB_NUMS_{i}' for i in range(1,6)]:\n",
    "    sub[col] = 0\n",
    "    \n",
    "sub = sub[['ID',\n",
    "           'CPU_USAGE_1', 'LAUNCHING_JOB_NUMS_1', \n",
    "           'CPU_USAGE_2', 'LAUNCHING_JOB_NUMS_2', \n",
    "           'CPU_USAGE_3', 'LAUNCHING_JOB_NUMS_3', \n",
    "           'CPU_USAGE_4', 'LAUNCHING_JOB_NUMS_4', \n",
    "           'CPU_USAGE_5', 'LAUNCHING_JOB_NUMS_5']]\n",
    "\n",
    "print(sub.shape)\n",
    "sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>CPU_USAGE_1</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_1</th>\n",
       "      <th>CPU_USAGE_2</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_2</th>\n",
       "      <th>CPU_USAGE_3</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_3</th>\n",
       "      <th>CPU_USAGE_4</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_4</th>\n",
       "      <th>CPU_USAGE_5</th>\n",
       "      <th>LAUNCHING_JOB_NUMS_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>86</td>\n",
       "      <td>0</td>\n",
       "      <td>85</td>\n",
       "      <td>0</td>\n",
       "      <td>93</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>57</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  CPU_USAGE_1  LAUNCHING_JOB_NUMS_1  CPU_USAGE_2  LAUNCHING_JOB_NUMS_2  \\\n",
       "0   1           90                     0           86                     0   \n",
       "1   2           38                     0           32                     0   \n",
       "2   3           38                     0           57                     0   \n",
       "3   4           23                     0           16                     0   \n",
       "4   5            3                     0            6                     0   \n",
       "5   6           13                     0           14                     0   \n",
       "6   7           10                     0            8                     0   \n",
       "7   8            0                     0            0                     0   \n",
       "8   9            3                     0            4                     0   \n",
       "9  10           15                     0           10                     0   \n",
       "\n",
       "   CPU_USAGE_3  LAUNCHING_JOB_NUMS_3  CPU_USAGE_4  LAUNCHING_JOB_NUMS_4  \\\n",
       "0           85                     0           93                     0   \n",
       "1           35                     0           42                     0   \n",
       "2           13                     0            9                     0   \n",
       "3            6                     0            4                     0   \n",
       "4           10                     0           10                     0   \n",
       "5           13                     0           11                     0   \n",
       "6            6                     0            2                     0   \n",
       "7            0                     0            0                     0   \n",
       "8            3                     0            3                     0   \n",
       "9           11                     0           10                     0   \n",
       "\n",
       "   CPU_USAGE_5  LAUNCHING_JOB_NUMS_5  \n",
       "0           92                     0  \n",
       "1           27                     0  \n",
       "2            1                     0  \n",
       "3            3                     0  \n",
       "4            9                     0  \n",
       "5           10                     0  \n",
       "6            3                     0  \n",
       "7            0                     0  \n",
       "8            3                     0  \n",
       "9           11                     0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 注意: 提交要求预测结果需为非负整数, 包括 ID 也需要是整数\n",
    "\n",
    "sub['ID'] = sub['ID'].astype(int)\n",
    "\n",
    "for col in [i for i in sub.columns if i != 'ID']:\n",
    "    sub[col] = sub[col].apply(np.floor)\n",
    "    sub[col] = sub[col].apply(lambda x: 0 if x<0 else x)\n",
    "    sub[col] = sub[col].astype(int)\n",
    "    \n",
    "sub.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((2996, 11), (2996, 11))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub.shape, sub_sample.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "sub.to_csv('baseline.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
